The invention relates to the field of enhancements for spreadsheet software instruments. More specifically the invention relates to an improved environment and data interface for spreadsheet and database instruments.
Heretofore three kinds of instruments had been involved with a similar idea as that dealt with in the invention: multitasking environments (A), database systems (B) and database tools for spreadsheet instruments (C).
A. Referring to MULTITASKING ENVIRONMENTS, the DESKVIEW.TM. multitasking environment, by Quarterdeck Office Systems, and the MICROSOFT WINDOWS.TM. graphical user interface and multitasking environment, by Microsoft Corporation, allow applications such as spreadsheets and database programs to transfer information from one to another by data exchange services. But this communication between processes is slow and limited to information displayed in the video screen.
B. Referring to relational DATABASE SYSTEMS, some of these let the user build entry tables that are similar in appearance to spreadsheet tables. The FOXBASE+.TM. and the FOXPRO.TM. programmable database file managers, by Fox Software Inc., call them browse tables. There are also other systems, such as the ADVANCED REVELATION.TM. programmable database file manager, by Revelation Technologies Inc., that let the user write small programs to perform field calculations that involve formulas and numerical operations. The LOTUS/DBMS.TM. spreadsheet/database integrative program, by Lotus Development Corporation, is one such relational database systems. A publication in the LOTUS.TM. industry-specific magazine, by Lotus Development Corporation, April 1988, page 5, says that the LOTUS/DBMS.TM. spreadsheet/database integrative program, by Lotus Development Corporation, "will share a common menu structure and programming capability with Lotus Development's forthcoming graphical spreadsheet" (referring to the LOTUS 1-2-3/G.TM. spreadsheet program, by Lotus Development Corporation). "This means, for example, that (the user) will be able to move data between the database and the spreadsheet without reformatting or converting." "[Besides] An add-in product will allow you to use LOTUS 1-2-3.TM. Releases 2 and 3 with the LOTUS/DBMS.TM. server. This will enable users of Microsoft's DOS 3.0.TM. operating system and OS/2.TM. operating system to share information, using the familiar LOTUS 1-2-3.TM. interface."
However, browse tables of relational database systems are not spreadsheet tables. In a browse table the user may define a formula and place its computed results in a field that is added to all the records. Thus, the same formula is repeatedly applied to all records. Except for the ADVANCED REVELATION.TM. programmable database file manager, by Revelation Technologies Inc., the formula's arguments may only be fields of the same record that holds the result of the calculation.
Most relational database instruments provide procedural languages for the development of applications. These languages allow the user to perform calculations with diverse structures, and write the results back to fields located anywhere in the records. But the combination of the procedural language and the data interface for indexed files causes the programming and interpretation of calculations to be cumbersome.
The LOTUS/DBMS.TM. spreadsheet/database integrative program, by Lotus Development Corporation, is an example of a standalone relational database instrument that operates as described above. Compatibility with the LOTUS 1-2-3.TM. spreadsheet program, by Lotus Development Corporation, is limited to menu structure and programming language. The LOTUS/DBMS.TM. spreadsheet/database integrative program, by Lotus Development Corporation, has offered an add-in product that allows the use of LOTUS 1-2-3 RELEASE 2.0.TM. and LOTUS 1-2-3 RELEASE 3.0.TM. with the LOTUS/DBMS.TM. spreadsheet/database integrative program, by Lotus Development Corporation. But it is very probable that this new product operates like all database add-ins for the LOTUS 1-2-3.TM. spreadsheet program, by Lotus Development Corporation. Database add-ins are very similar to the @BASE.TM. spreadsheet add-in for database management, by Personics Corporation, a database tool for a spreadsheet program described in the following section.
C. The ideas used by DATABASE TOOLS FOR SPREADSHEET PROGRAMS are the most similar prior-art references to the invention.
The @BASE.TM. spreadsheet add-in for database management, by Personics Corporation, may be considered the pioneer among the LOTUS 1-2-3.TM. add-in product family. It has kept a leadership position in the market and a series of enhanced versions have updated its capabilities.
The @BASE.TM. spreadsheet add-in for database management is a database add-in for the LOTUS 1-2-3.TM. spreadsheet program that mimics most commands in the DBASE III.TM. database file manager, by Ashton Tate Corporation. The manufacturer refers to it as "a full-feature relational database add-in for LOTUS 1-2-3.TM.." Personics advertises the product with the slogan: "@BASE.TM. turns LOTUS 1-2-3.TM. into a powerful front-end for database applications."
In the @BASE.TM. spreadsheet add-in for database management it is possible to set up dynamic links that import updated information from the DBASE III.TM. database file manager records every time a worksheet is recalculated. Records can be entered or examined either in a form view, where each record occupies a separate screen, or in the usual one-record-to-a-line format.
Besides the indexing feature, the @BASE.TM. spreadsheet add-in for database management's OPTION PAC.TM., by Personics Corporation, can perform file join operations to create relational databases and operating computed fields. Also it may sort a database without rearranging the records on the disk, and automatically keeps files in the correct sorting order during record entry or updating.
The @BASE.TM. spreadsheet add-in for database management comprises two sets of elements in its operational structure: a functional replicate of the DBASE III.TM. database file manager's set of commands, and a set of @functions for read and write operations.
Any DBASE III.TM.-compatible command may be issued from within the LOTUS 1-2-3.TM. spreadsheet program, by Lotus Development Corporation. But the @BASE.TM. spreadsheet add-in for database management and the LOTUS 1-2-3.TM. spreadsheet program have completely independent video screens and communication links between files and data. Thus, operating the @BASE.TM. spreadsheet add-in for database management's independent compatible format files is functionally equivalent to suspending execution of the LOTUS 1-2-3.TM. spreadsheet program, and activating the DBASE III.TM. database file manager. The user may go back to the LOTUS 1-2-3.TM. spreadsheet program when finished using the DBASE III.TM. tools. This clears the video overlay of the add-in application, and allows the user to return to the LOTUS 1-2-3.TM.'s previous condition.
A set of @functions allow the user to read and write the contents of single fields in database files and single cells in the LOTUS 1-2-3.TM. spreadsheet environment. These functions perform like standard screen input and output operations in the DBASE III.TM. database file manager. Yet database and spreadsheet instruments operate independently from one another.
With the invention, the user can generate coordinated video operations, to look and operate simultaneously at a combination of the spreadsheet and the database files contents. This is not possible in the @BASE.TM. spreadsheet add-in for database management.
By means of the invention, the user needs only design the structure and contents of database files and spreadsheet formulas. The screen simultaneously displays actual spreadsheet information and a dynamic database browse view. Yet, the program performs all read and write functions automatically, so the user is unaware of them. Thus, it is necessary for the user to distinguish between the database and the spreadsheet data.
In the @BASE.TM. spreadsheet add-in for database management, the user has to program each individual read and write operation to and from the database files. These operations and functions may be directly written in data cells or as the LOTUS 1-2-3.TM. spreadsheet program's macro commands. Yet an average programmer would consider this an extensive and complicated programming task. The execution of these operations would be significantly slow, due to the large number of commands and to a sub-optimal execution schedule for read and write operations.
The ORACLE DATABASE ADD-IN FOR 1-2-3.TM. spreadsheet add-in for database management, by Oracle Systems Corp., is a product whose basic concept is similar to the @BASE.TM. spreadsheet add-in for database management. However, this product also allows the user to type SQL statements directly into the worksheet. These statements can be stored in LOTUS 1-2-3.TM. worksheet cells as LOTUS 1-2-3.TM. @functions. Function @SQL(range) executes the SQL statement in "range." These SQL statements may operate directly with database tables.
However, video operations in the environments of LOTUS 1-2-3.TM. and the ORACLE DATABASE ADD-IN FOR 1-2-3.TM. spreadsheet add-in are totally isolated, as in the @BASE.TM. spreadsheet add-in for database management. With the invention, the user can generate coordinated video operations, to look and operate simultaneously at a combination of the spreadsheet and the database files contents, as was described before.
SQL statements accelerate programming for the operation of database files, but they cannot control those read and write operations that effect information transfer between spreadsheet and database instruments. Users of the ORACLE DATABASE ADD-IN FOR 1-2-3.TM. spreadsheet add-in would have to do extensive programming automatic read/write operations in the invention. Therefore, the same limitations described for the @BASE.TM. spreadsheet add-in for database management, by Personics Corporation, apply to this product.
The INFORMIX DATASHEET ADD-IN.TM. spreadsheet add-in for database management, by Informix Software Inc., develops essentially the same idea and data procedures as the @BASE.TM. spreadsheet add-in for database management, by Personics Corporation, but is based in a different database file format. It uses "query by example" techniques. There are other important implementations of database add-in spreadsheet tools, such as @DB123.TM. , which are not described here. These present similar characteristics and limitations as those products described here.
As mentioned in preceding paragraphs, the invention presents a spreadsheet environment optimally suited for the integrated operation of database files. Such an environment cannot be developed using an add-in product such as any of those described here. This type of product is designed with a different orientation. It provides the user expanded spreadsheet operations, by adding @functions and superimposing alternative menus and video structures in particular modes of the spreadsheet environment. These capabilities in an existent spreadsheet product do not suffice for the implementation of the invention.
Recent spreadsheet programs, like the MICROSOFT EXCEL.TM. spreadsheet program, by Microsoft Corporation, the SUPERCALC.TM. spreadsheet program, by Computer Associates International Inc., and the LOTUS 1-2-3 release 2.2.TM. and the LOTUS 1-2-3 release 3.1.TM. spreadsheet programs, by Lotus Development Corporation, also contain database features. As an example of their characteristics, four products will be discussed here: the QUATTRO PRO.TM. spreadsheet program, by Borland International, the VP-PLANNER PLUS.TM. spreadsheet program, by Paperback Software International, the LOTUS SYMPHONY.TM. integrated spreadsheet/database manager/word processing program, by Lotus Development Corporation, and the UNIPLEX SPREADSHEET.TM. program by Uniplex Information Systems.
The QUATTRO PRO.TM. spreadsheet program, by Borland International, is a spreadsheet program that implements database instruments similar to those described in the previous add-in database instruments. The same limitations described for the @BASE.TM. spreadsheet add-in for database management, by Personics Corporation, apply to this product. Also, this product does not allow the user to write information directly to a database file. Besides, the user may read data from a database file, but needs to convert it to a spreadsheet format to operate on it.
The VP-PLANNER PLUS.TM. spreadsheet program, by Paperback Software International, is a spreadsheet program that implements the read, write, query and sort operations of the previous database instruments. This product also implements a multidimensional database option, which lets the user create a database with up to five fields. By choosing two of these fields to act as vertical and horizontal coordinates, the user can view a crosstab table automatically. However, this multidimensional database file is really a spreadsheet data storage file of a fixed format. Thus, the same limitations described for the @BASE.TM. spreadsheet add-in for database management, by Personics Corporation, apply to this product.
The LOTUS SYMPHONY.TM. integrated spreadsheet/database manager/word processing program, by Lotus Development Corporation, integrates spreadsheet, database and word processing environments. Yet this product stores database information in spreadsheet cells. So, the nature of its query, sort and other database operations is very similar to operations in LOTUS 1-2-3.TM. and other spreadsheet programs. The same limitations described for the @BASE.TM. spreadsheet add-in for database management, by Personics Corporation, apply to this product.
The UNIPLEX SPREADSHEET.TM. program by Uniplex Information Systems, is a spreadsheet program based in the UNIX.TM. operating system, by AT&T. It supports "embedded calls to the database using SQL," according to the commercial documentation. The UNIPLEX SPREADSHEET.TM. program by Uniplex Information Systems, is part of the UNIPLEX ADVANCED OFFICE SYSTEM.TM. also by Uniplex Information Systems, integrating several application programs. However, all these products can only exchange information through a compatible format for file storage. Like most integrated software systems, the UNIPLEX SPREADSHEET.TM. program only allows video integration of independent modules through the window capability of the base operating systems. The same limitations described for the @BASE.TM. spreadsheet add-in for database management, by Personics Corporation, and for multitasking environments such as the DESKVIEW.TM. multitasking environment, by Quarterdeck Office Systems, and the MICROSOFT WINDOWS.TM. graphical user interface and multitasking environment, by Microsoft Corporation, apply to this product.
Two particular products also merit mention as relevant prior-art references to the invention: (D) the REFLEX PLUS.TM. spreadsheet analysis tool, by Borland International, and (D) the RONSTADT'S FINANCIALS.TM. financial forecasting system, by Lord Publishing Inc.
D. The REFLEX PLUS.TM. spreadsheet analysis tool, by Borland International, is a database program that adds special features for the analysis of information. The user may define several ways of displaying the data, or database "views."
For example, one possible "view," called table-mode, can mimic some operations in a spreadsheet program. This corresponds to the "browse" mode in other database programs. It allows the user to examine all the database information as in a spreadsheet table.
Table-mode operations act only on the structure and contents of a single flat database file. In table-mode, any formula is evaluated on all records of the underlying database. This is functionally equivalent to the operation of computed fields in other database programs. The user interface for the development, modification and recalculation of formulas in table-mode imitates some characteristics of spreadsheet programs.
The REFLEX PLUS.TM. spreadsheet analysis tool is a flat database program with a powerful customized interface for data analysis. Yet its functionality is still limited to that of a database program, which naturally lacks the flexibility of spreadsheet operations. Thus the restrictions mentioned for relational databases apply to this program as well.
E. The RONSTADT'S FINANCIALS.TM. financial forecasting system, by Lord Publishing Inc., is a software tool for developing financial models (by Lord Publishing, Inc.; One Apple Hill, Natick, Mass. 01760; 508-651-9955).
The visual operation and the user interface of the RONSTADT'S FINANCIALS.TM. financial forecasting system are similar to the operation of the invention's database interface. Also, the RONSTADT'S FINANCIALS.TM. financial forecasting system allows the definition of formulas as in a spreadsheet, but the user is restricted to write them in a single column. This instrument is not a spreadsheet program.
Formula evaluation in the RONSTADT'S FINANCIALS.TM. financial forecasting system parallels a Copy command in a spreadsheet, executed over the entire worksheet. The user of the RONSTADT'S FINANCIALS.TM. financial forecasting system loads a column of formulas and an entire set of database records into memory. The screen displays records as a columns of data. Then the column of formulas operates repeatedly on successive data columns.
As indicated before the RONSTADT'S FINANCIALS.TM. financial forecasting system is not a spreadsheet program. Therefore, it lacks the flexibility and efficiency of the spreadsheet operations that the invention allows. It is restricted by the columnar evaluation of formulas.
The RONSTADT'S FINANCIALS.TM. financial forecasting system does not allow the operation of external database files, because it operates exclusively on the database file that is loaded completely into memory. The invention, on the other hand, operates directly with database files' contents.
The RONSTADT'S FINANCIALS.TM. financial forecasting system was designed exclusively for the calculation of financial projections. Therefore, it is limited to working with data columns that represent single periods in time. The invention, being an enhancement to a spreadsheet instrument, does not have this restriction, since it can be used to develop all kinds of applications.
F. SPREADSHEET COMPILERS also merit mention as relevant prior-art references. Two examples of these are the BALER.TM. spreadsheet compiler, by Baler Software Corporation, and the KING JAGUAR.TM. spreadsheet compiler, by Sheng Laboratories Inc.
These instruments allow the user to compile or convert a worksheet into a closed (unmodifiable) application. The worksheet may be developed with any spreadsheet program, such as the LOTUS 1-2-3.TM. spreadsheet program. The result is a standalone program that uses context-sensitive help screens, data validation, special macros, and pull-down menus. These instruments do not allow the modification of formulas after compilation, nor the creation of new formulas. Therefore, the finished application loses the flexibility of the spreadsheet.
The invention allows the user all the features mentioned above WITHIN an active modifiable spreadsheet. The invention enables the user to continue to work with the spreadsheet, modify formulas and screens, and do additional transformations on the data. Also, all existing spreadsheet compilers do not contain the capability to access external database files.
The cited PRIOR-ART references are important because the invention provides the benefits of integrating their main characteristics. Like database add-ins, the invention is a spreadsheet enhancement. Yet, it is the only spreadsheet enhancement that implements options for full environment definition and automatic read and write operations between database and spreadsheet structures. The RONSTADT'S FINANCIALS.TM. financial forecasting system, by Lord Publishing Inc., and the REFLEX PLUS.TM. spreadsheet analysis tool, by Borland International, store their information in database structures similarly to the invention. Also, the RONSTADT'S FINANCIALS.TM. financial forecasting system allows formula operation on database structures. Yet, the invention integrates these concepts in spreadsheet instruments. Spreadsheet compilers implement options for full environment definition, but the invention does so while keeping the interactive nature of a full spreadsheet environment.
Thus, as stated before, the invention allows users to interactively develop new environments to program and use finished applications, and to use database files for the storage of information operated in the spreadsheet program. Prior art references do not implement interactive creation of spreadsheet environments.
Accordingly, the following paragraphs describe several objects and advantages of the invention.
The invention expands the power of spreadsheet programs without placing any limitations on their basic operations. All applications developed for spreadsheet environments are a primary subject of the invention. Of particular interest are applications whose data structures benefit from the added support of database files.
The invention integrates the operation of spreadsheet and database instruments in an environment that provides an "automatic" collaborative scheme. It allows the coordination of video screens, data storage, spreadsheet operations and database features. The combination does not confuse the user by presenting a multitude of options. This gives more flexibility and power to the spreadsheet than all 3-D and "@BASE-like" schemes used in other products.
The invention enables the user to develop spreadsheet application environments. This will enable users to develop small, practical and modifiable applications for tasks such as the analysis of financial and accounting statements and market reports. Finished applications may provide specific database features. Also, they may have data validation capabilities, context-sensitive help texts and distinctive screen displays.
The invention's synergistic approach allows users to develop spreadsheet applications traditionally executed with database instruments, such as integrated accounting packages and inventory analysis.
By using the invention, users can develop small, practical and modifiable applications for the analysis of financial and accounting statements, market demand, and other business problems. These types of applications will benefit from the invention, because they require relational database storage and access techniques, and complex analytical computations.
For example, a simple financial model can be developed by using only one col.sub.-- prog window and one flat database file. In this application, each record in the database file corresponds to one period. Also, all formulas are developed in the col.sub.-- prog window. The implementation of a large-scale financial model may use separate database files to store credit information, accounting data, market demand analysis, etc. It would take approximately three man-years to implement this model with prior-art tools. In the invention's environment, the development of the model takes between three and six months.
Therefore, the invention represents cost reductions in the development of many types of large and complicated data-analysis applications. The resulting applications are as flexible as spreadsheet programs, and modifiable by the user.
Another example is inventory planning. The invention enables the user to design an application containing past history of sales, inventory levels, prices and other criteria for each product and/or product group. The user may easily classify products according to their sales/inventory level indexes. The application allows the user to prepare sales forecasts and recommend new orders for each product and/or product group. This is accomplished by one or more associated database files. Each file may contain, among other data, information regarding individual products, product groups, demand forecasts, and future orders for manufacturing and/or raw materials.
Another example is a fully integrated accounting system. The invention allows the creation of such a system in a spreadsheet-database environment. The scratchpad section allows the user the additional opportunity of performing spreadsheet operations on data generated by reports. This information is stored in a relational structure of database files associated to several col.sub.-- prog windows.
Database files being used by the invention may contain special operational codes. These codes can control the operation of specific database fields or spreadsheet cells. In a particular example, operational codes allow the results of a financial application to be calculated and expressed in different currencies. This may be done without changing the formulas in the spreadsheet environment. Thus, operational codes expand the power and flexibility of the invention's programmability.
The invention has the functionality of the combination of a spreadsheet compiler and a spreadsheet program. A user can develop applications in a spreadsheet format. Then the user may define tools for the application (menu options, help screens, data validation routines, etc.) that enable other users to operate the application.
The invention overcomes weaknesses that proved the downfall of spreadsheet prepackaged applications. These applications for spreadsheet programs, or "templates," reached their sales peak before the arrival of the LOTUS 1-2-3.TM. spreadsheet program. According to some experts, their popularity wanned gradually due to their failure to make the spreadsheet program easier to use. (See chapter 3 of: Entrepreneurial Finance--Taking Control of Your Financial Decision Making, by Dr. Robert Ronstadt; Lord Publishing, Natick, Mass., 1988. Also see: Mark Chussil, "Computer-Based Planning: A PC Survival Guide," The Journal of Business Strategy, January/February, 1988, pp. 38-42.)
Users of the invention are able to interactively modify an application developed using the invention. The user who developed it can limit the number and quality of the modifications available to other users. Thus, any other user can build new routines for any application, that conform to specific needs. These routines involve adding formulas that cannot damage the primitive application.
The invention can operate as the software equivalent of a hardware parallel processor for several Turing machines. Additionally, some tools of the invention can make it operate more efficiently than a the parallel Turing machines. Any computation or recognition problem for which there is a known informal algorithm can be handled by a Turing machine. Therefore, the invention can handle and solve all sorts of programming problems.
Templates developed for spreadsheet programs do not execute as efficiently as other programming means currently available. However, spreadsheet programming is now popular due to the interactive nature of its operation. Also, at a medium range of time, faster mass-memory devices will be available. Then, multidimensional database structures coupled with spreadsheet program tools will make the invention more attractive. Execution of applications programmed with the invention will be more efficient.
This combination of concepts is an important ramification of the long-term application of the invention. During the next ten years, a large portion of software development and research will focus on the creation of tools for power-easy programming and the easy use of applications. Hardware storage devices will change. Database files will probably be stored in dynamic memory, bubble memory and other hardware structures. This will result in faster data operations.
The storage and processing of the three elements of the invention (database structures, spreadsheet structures and video system structures) will be important in terms of their relative structures or topologies. The invention may represent a future direction for research. The user will have more power to develop and operate data structures. Also, the user will be able to control the corresponding three levels of the development process for products, tools and applications.
Further objects and advantages of the invention will become apparent in the consideration of the drawings and ensuing description.